set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sprMayPhaMauReport]
	-- Add the parameters for the stored procedure here
			@Month int,
			@Year int,
			@LoaiMay int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

SELECT ao.AccountingObjectCode as MaKH, 
        ao.AccountingObjectName as TenKH,
        ii.InventoryItemCode AS MaHang,
        ISNULL(iic.InventoryCategoryCode, '') as PhanLoaiHang, 
        ii.InventoryItemName as TenHang, 
        CASE WHEN (vfp.CreditAccount LIKE '%511%' OR vfp.CreditAccount LIKE '%512%') THEN SUM(vfp.Amount + vfp.VATAmount) ELSE 0 END as DoanhSoTrongThang , 
		DoanhSoToiThieu,
		MONTH(vfp.PostedDate) as 'Month',
		YEAR(vfp.PostedDate) as 'Year',
		ThoiDiemLapMay
FROM	[ATA]..KhachHangLapMayPhaMau KH 
INNER JOIN [ATAHANOI]..accountingObject ao ON KH.KhachHangID = ao.AccountingObjectID
LEFT JOIN [ATAHANOI]..View_VoucherForPayable vfp ON vfp.AccountingObjectID = ao.AccountingObjectID
LEFT JOIN [ATAHANOI]..InventoryItem ii ON vfp.InventoryItemID = ii.InventoryItemID
LEFT JOIN [ATAHANOI]..InventoryItemCategory iic ON  ii.InventoryCategoryID = iic.InventoryCategoryID

WHERE
	LoaiMay = @LoaiMay AND 
   --     (vfp.CreditAccount IS NULL) OR (vfp.CreditAccount LIKE '%511%' OR vfp.CreditAccount LIKE '%512%'))  
         (vfp.Posted=1 OR vfp.Posted IS NULL)
        AND ((DATEPART(month, vfp.PostedDate) BETWEEN (DATEPART(month, ThoiDiemLapMay)) AND @Month 
			AND DATEPART(year, vfp.PostedDate) BETWEEN  (DATEPART(Year, ThoiDiemLapMay))  AND  @Year) OR vfp.PostedDate IS NULL )
		AND ((ISNULL(iic.InventoryCategoryCode, '') LIKE '%BS%') OR iic.InventoryCategoryCode IS NULL)
		AND (vfp.PostedDate > KH.ThoiDiemLapMay OR vfp.PostedDate IS NULL)
GROUP BY ao.AccountingObjectCode, ao.AccountingObjectName, 
		ii.InventoryItemID, ii.InventoryItemCode, 
		iic.InventoryCategoryCode, ii.InventoryItemName,
		YEAR(vfp.PostedDate), MONTH(vfp.PostedDate),
		DoanhSoToiThieu, vfp.CreditAccount,
		ThoiDiemLapMay
ORDER BY TenKH, InventoryCategoryCode
END

